package com.ndp.fb.rdb.db.impl;

import com.alibaba.dubbo.config.annotation.Service;
import com.esotericsoftware.minlog.Log;
import com.mysql.jdbc.Statement;
import com.ndp.fb.constant.CommonConstants;
import com.ndp.fb.rdb.api.ConnectionObjectRdbService;
import com.ndp.fb.rdb.db.DaoTemplate;
import com.ndp.fb.rdb.db.impl.mapper.DefaultRowMapper;
import com.ndp.fb.rdb.model.ConnObjectProductConfig;
import com.ndp.fb.rdb.model.ConnectionObject;
import com.ndp.fb.rdb.model.ProductConfiguration;
import com.ndp.fb.redis.api.RedisClient;
import com.ndp.fb.redis.api.RedisDistributedLockService;
import com.ndp.fb.util.ListUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by infi on 2015/1/29.
 */
@Service
public class ConnectionObjectRdbServiceImpl extends DaoTemplate implements ConnectionObjectRdbService {

    @Autowired
    private RedisDistributedLockService redisDistributedLockService;
    
    @Autowired
    RedisClient redisClient;

    @Override
    public ConnectionObject findByFbConnectionObjectIdAndType(Long connectionobjectId, Integer type) {
        String sql = "select * from connection_object where fb_connection_object_id = ? and type = ?";
        return findOne(sql, new Object[]{connectionobjectId, type}, DefaultRowMapper.autoRowMapper(ConnectionObject.class));
    }



    @Override
    public ConnectionObject findOneByFbConnectionObjectId(Long fbConnectionObjectId) {
        String sql = "select * from connection_object where fb_connection_object_id = ?";
        return findOne(sql, new Object[]{fbConnectionObjectId}, DefaultRowMapper.autoRowMapper(ConnectionObject.class));
    }


    /**
     * @param fbAccountId
     * @param type
     * @return
     * @discription
     * @author giser.yang
     * 创建时间 2015年6月3日 上午11:50:19
     * @see com.ndp.fb.rdb.api.ConnectionObjectRdbService#findByFbAccountId(java.lang.Long, java.lang.Integer)
     */

    @Override
    public List<ConnectionObject> findByFbAccountId(Long fbAccountId,
                                                    Integer type) {
        String sql = "SELECT t1.* FROM connection_object t1 WHERE t1.type = ? and t1.fb_connection_object_id in (SELECT t2.fb_connection_object_id FROM entrust_account_connection_objects t2 WHERE t2.fb_account_id = ?)";
        return getJdbcTemplate().query(sql, new Object[]{type, fbAccountId}, DefaultRowMapper.autoRowMapper(ConnectionObject.class));
    }

    /**
     * @param userId
     * @param type
     * @return
     * @discription
     * @author giser.yang
     * 创建时间 2015年6月3日 下午4:40:25
     */

    @Override
    public List<ConnectionObject> findByUserId(String userId, Integer type) {
//        String sql = "SELECT\n" +
//                "	t1.*\n" +
//                "FROM\n" +
//                "	connection_object t1\n" +
//                "WHERE\n" +
//                "	t1.type = ? \n" +
//                "AND t1.fb_connection_object_id IN (\n" +
//                "	SELECT\n" +
//                "		t2.fb_connection_object_id\n" +
//                "	FROM\n" +
//                "		entrust_account_connection_objects t2\n" +
//                "	WHERE\n" +
//                "		t2.fb_account_id IN (\n" +
//                "			SELECT\n" +
//                "				t3.fb_account_id\n" +
//                "			FROM\n" +
//                "				fb_userpermissions_adaccounts t3\n" +
//                "			WHERE\n" +
//                "        t3.role in ('ADMIN','GENERAL_USER')\n" +
//                "				and t3.`status` = 'ACTIVE'\n" +
//                "				and t3.fb_user_id IN (\n" +
//                "					SELECT\n" +
//                "						t4.fb_user_id\n" +
//                "					FROM\n" +
//                "						fb_user t4\n" +
//                "					WHERE\n" +
//                "						t4.user_id =? \n" +
//                "				)\n" +
//                "		)\n" +
//                ")";
        String sql = "SELECT\n" +
                "	DISTINCT conn.*\n" +
                "FROM\n" +
                "	connection_object conn,\n" +
                "	entrust_account_connection_objects eac,\n" +
                "	fb_userpermissions_adaccounts fua,\n" +
                "  fb_user fuser,\n" +
                "product_configuration pc\n" +
                "where conn.fb_connection_object_id = eac.fb_connection_object_id\n" +
                "and eac.fb_account_id = fua.fb_account_id\n" +
                "and fuser.fb_user_id = fua.fb_user_id\n" +
                "and conn.fb_connection_object_id = pc.fb_connection_object_id\n" +
                "and fua.role in ('ADMIN','GENERAL_USER')\n" +
                "and fua.status = 'ACTIVE'\n" +
                "and pc.platform <> 'fb_canvas'\n" +
                "and conn.type = ? \n" +
                "and fuser.user_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{type, userId}, DefaultRowMapper.autoRowMapper(ConnectionObject.class));
    }

    @Override
    public List<ConnectionObject> findOwnerConnObjByUserId(String userId)
    {
        String sql = "SELECT DISTINCT\n" +
                "\tconnt.id,\n" +
                "\tconnt.*\n" +
                "FROM\n" +
                "\tfb_user fbuser,\n" +
                "\tfb_userpermissions_adaccounts fua,\n" +
                "\tentrust_account ent,\n" +
                "\tentrust_account_connection_objects entcon,\n" +
                "\tconnection_object connt,\n" +
                "\tproduct_configuration p\n" +
                "WHERE\n" +
                "\tfbuser.fb_user_id = fua.fb_user_id\n" +
                "AND fua.fb_account_id = ent.fb_account_id\n" +
                "AND fua.role IN ('GENERAL_USER', 'ADMIN')\n" +
                "AND fua. STATUS = 'ACTIVE'\n" +
                "AND entcon.fb_account_id = ent.fb_account_id\n" +
                "AND entcon.fb_connection_object_id = connt.fb_connection_object_id\n" +
                "AND connt.fb_connection_object_id = p.fb_connection_object_id\n" +
                "AND connt.type = 2\n" +
                "AND p.platform <> 'fb_canvas'\n" +
                "AND fbuser.user_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{userId}, DefaultRowMapper.autoRowMapper(ConnectionObject.class));
    }

    @Override
    public ConnectionObject findByUserIdAndAppId(String userId, String appId) {
        String sql = "SELECT\n" +
                "	DISTINCT conn.*\n" +
                "FROM\n" +
                "	connection_object conn,\n" +
                "	entrust_account_connection_objects eac,\n" +
                "	fb_userpermissions_adaccounts fua,\n" +
                "  fb_user fuser,\n" +
                "product_configuration pc\n" +
                "where conn.fb_connection_object_id = eac.fb_connection_object_id\n" +
                "and eac.fb_account_id = fua.fb_account_id\n" +
                "and fuser.fb_user_id = fua.fb_user_id\n" +
                "and conn.fb_connection_object_id = pc.fb_connection_object_id\n" +
                "and fua.role in ('ADMIN','GENERAL_USER')\n" +
                "and fua.status = 'ACTIVE'\n" +
                "and pc.platform <> 'fb_canvas'\n" +
                "and conn.type = 2 \n" +
                "and conn.fb_connection_object_id = ? \n" +
                "and fuser.user_id = ?";
        List<ConnectionObject> query = getJdbcTemplate().query(sql, new Object[]{appId, userId}, DefaultRowMapper.autoRowMapper(ConnectionObject.class));
        if(query.size() > 0 ){
            return query.get(0);
        } else {
            return null;
        }
    }

    /**
     * @param fbUserId
     * @param type
     * @return
     * @discription
     * @author giser.yang
     * 创建时间 2015年6月3日 下午4:40:25
     * @see com.ndp.fb.rdb.api.ConnectionObjectRdbService#findByFbUserId(java.lang.Long, java.lang.Integer)
     */

    @Override
    public List<ConnectionObject> findByFbUserId(Long fbUserId, Integer type) {
        String sql = "SELECT t1.* FROM connection_object t1 WHERE t1.type = ? and t1.fb_connection_object_id in (SELECT t2.fb_connection_object_id FROM entrust_account_connection_objects t2 WHERE t2.fb_account_id IN ( SELECT t3.fb_account_id FROM fb_userpermissions_adaccounts t3 WHERE t3.fb_user_id = ?))";
        return getJdbcTemplate().query(sql, new Object[]{type, fbUserId}, DefaultRowMapper.autoRowMapper(ConnectionObject.class));
    }

    /**
     * @param fbAccountId
     * @return
     * @discription
     * @author giser.yang
     * 创建时间 2015年6月4日 下午7:05:37
     * @see com.ndp.fb.rdb.api.ConnectionObjectRdbService#findByFbAccountId(java.lang.Long)
     */

    @Override
    public List<ConnectionObject> findByFbAccountId(Long fbAccountId) {
        String sql = "SELECT t1.* FROM connection_object t1 WHERE t1.fb_connection_object_id in (SELECT t2.fb_connection_object_id FROM entrust_account_connection_objects t2 WHERE t2.fb_account_id = ?)";
        return getJdbcTemplate().query(sql, new Object[]{fbAccountId}, DefaultRowMapper.autoRowMapper(ConnectionObject.class));
    }

    @Override
    public List<ConnObjectProductConfig> findConnObjectProductConfigdByUserIdAndType(String userId, Integer type) {


        List<ConnObjectProductConfig> connObjectProductConfigs = new ArrayList<ConnObjectProductConfig>();
        List<ConnectionObject> connectionObjectList = findByUserId(userId, type);
        ConnObjectProductConfig connObjectProductConfig;
        //去除平台重复
        String productSql = "select * from (SELECT p.* from product_configuration p where p.fb_connection_object_id=? ORDER BY id desc ) p1 GROUP BY p1.platform";
        for (ConnectionObject connectionObject : connectionObjectList) {
            connObjectProductConfig = new ConnObjectProductConfig();
            connObjectProductConfig.setConnectionObject(connectionObject);
            List<ProductConfiguration> productConfigurations = getJdbcTemplate().query(productSql, new Object[]{connectionObject.getFbConnectionObjectId()},
                    DefaultRowMapper.autoRowMapper(ProductConfiguration.class));
            if (!ListUtil.isEmpty(productConfigurations)) {
                connObjectProductConfig.setProductConfiguration(productConfigurations);
                connObjectProductConfigs.add(connObjectProductConfig);
            }
        }
        return connObjectProductConfigs;
    }

    @Override
    public Long saveConnectionObject(final ConnectionObject connectionObject) {
        return saveResultId(connectionObject);
    }

    /**
     * @discription
     * @author giser.yang
     * 创建时间 2015年6月29日 下午5:39:59
     * @see com.ndp.fb.rdb.api.ConnectionObjectRdbService#saveorupdateConnectionObject(com.ndp.fb.rdb.model.ConnectionObject)
     */

    @Override
    public Integer saveorupdateConnectionObject(
            ConnectionObject connectionObject) {
        String key = CommonConstants.CONNECTION_OBJECT_LOCK + connectionObject.getFbConnectionObjectId();
        if (redisDistributedLockService.tryLock(key)) {
            try {
                if (this.findOneByFbConnectionObjectId(connectionObject.getFbConnectionObjectId()) == null) {
                    this.saveConnectionObject(connectionObject);
                } else {
                    this.updateConnectionObject(connectionObject);
                }
            } catch (Exception ex) {
                Log.error(ex.getMessage(), ex);
            } finally {
                redisDistributedLockService.unLock(key);
            }
        }
        return 1;
    }

    /**
     * @param connectionObject
     * @return
     * @discription
     * @author giser.yang
     * 创建时间 2015年6月29日 下午5:49:11
     * @see com.ndp.fb.rdb.api.ConnectionObjectRdbService#updateConnectionObject(com.ndp.fb.rdb.model.ConnectionObject)
     */

    @Override
    public Integer updateConnectionObject(final ConnectionObject connectionObject) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        return getJdbcTemplate().update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con)
                    throws SQLException {
                String sql = "UPDATE connection_object " +
                        "SET type = ?, name = ?, native_app_store_ids = ?, native_app_targeting_ids = ?," +
                        " picture = ?, supported_platforms = ?, object_store_urls = ?, url = ?, logo_url = ?," +
                        " icon_url = ? WHERE fb_connection_object_id = ?";
                PreparedStatement ps = con.prepareStatement(sql,
                        Statement.RETURN_GENERATED_KEYS);
                ps.setObject(1, connectionObject.getType());
                ps.setObject(2, connectionObject.getName());
                ps.setObject(3, connectionObject.getNativeAppStoreIds());
                ps.setObject(4, connectionObject.getNativeAppTargetingIds());
                ps.setObject(5, connectionObject.getPicture());
                ps.setObject(6, connectionObject.getSupportedPlatforms());
                ps.setObject(7, connectionObject.getObjectStoreUrls());
                ps.setObject(8, connectionObject.getUrl());
                ps.setObject(9, connectionObject.getLogoUrl());
                ps.setObject(10, connectionObject.getIconUrl());
                ps.setObject(11, connectionObject.getFbConnectionObjectId());
                return ps;
            }
        }, keyHolder);
    }


    /** 
     * @discription
     * @author giser.yang       
     * 创建时间 2015年8月12日 下午6:21:30      
     * @param fbConnectionObjectId
     * @return     
     * @see com.ndp.fb.rdb.api.ConnectionObjectRdbService#findFromCache(java.lang.Long)     
     */  
        
    @Override
    public ConnectionObject findFromCache(Long fbConnectionObjectId) {
        ConnectionObject connectionObject = (ConnectionObject)redisClient.get("fbapp"+fbConnectionObjectId);
        if(connectionObject==null){
            connectionObject = this.findOneByFbConnectionObjectId(fbConnectionObjectId);
            if(connectionObject!=null){
                redisClient.set("fbapp"+fbConnectionObjectId, connectionObject);
            }
        }
        return connectionObject;
    }

    @Override
    public List<ConnectionObject> findByOrgIdAndType(Long orgId, int type) {
        String sql = "select DISTINCT c.* from connection_object c,user,fb_user,fb_userpermissions_adaccounts fua,\n" +
                "entrust_account_connection_objects ec,product_configuration p\n" +
                "where `user`.ticket = fb_user.user_id\n" +
                "and fb_user.fb_user_id = fua.fb_user_id\n" +
                "and fua.role in ('ADMIN','GENERAL_USER')\n" +
                "and fua.status = 'ACTIVE'\n" +
                "and fua.fb_account_id = ec.fb_account_id\n" +
                "and ec.fb_connection_object_id = c.fb_connection_object_id\n" +
                "and p.fb_connection_object_id = c.fb_connection_object_id\n" +
                "and p.platform <> 'fb_canvas'\n" +
                "and c.type = ? " +
                "and user.org_id = ?";
        return getJdbcTemplate().query(sql, new Object[]{type,orgId}, DefaultRowMapper.autoRowMapper(ConnectionObject.class));
    }
}
